import pymysql
import uuid
import pandas as pd
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')

# 修改表结构，将 id 字段改为 varchar 以存储 UUID
sql = '''  
create table if not exists Professional (  
    id varchar(255) primary key,  
    Professional_type varchar(225),  
    Professional_Code varchar(225),  
    Professional_name varchar(225),  
    Degree_award_category varchar(225),  
    years_of_study varchar(225),  
    add_year varchar(225)  
)   
'''
db_handler.cursor.execute(sql)

df = pd.read_excel('C:/Users/姚宇康/Desktop/日常文件/学习/专业.xlsx')
df.rename(columns={
    '门类、专业类': 'Professional_type',
    '专业代码': 'Professional_Code',
    '专业名称': 'Professional_name',
    '学位授予门类': 'Degree_award_category',
    '修业年限': 'years_of_study',
    '增设年度': 'add_year'
}, inplace=True)
# 填充空值
df.fillna('', inplace=True)

# 生成 UUID 并添加到 DataFrame
df['id'] = ['P' + str(uuid.uuid4())[:8] for _ in range(len(df))]

# 将 DataFrame 转换为列表的元组（因为 executemany 需要这种格式）
# 但由于我们只使用 execute 而不是 executemany，这里我们不需要转换为列表的元组
# 但为了清晰起见，我们仍然可以这样处理
data_to_insert = df.to_dict(orient='records')

# 插入数据
sql_insert = 'INSERT INTO Professional (id, Professional_type, Professional_Code, Professional_name, Degree_award_category, years_of_study, add_year) VALUES (%s, %s, %s, %s, %s, %s, %s)'
try:
    for record in data_to_insert:
        db_handler.cursor.execute(sql_insert, (
            record['id'],
            record['Professional_type'],
            record['Professional_Code'],
            record['Professional_name'],
            record['Degree_award_category'],
            record['years_of_study'],
            record['add_year']
        ))
        print(db_handler.cursor.rowcount, "记录插入成功。")
except pymysql.MySQLError as e:
    print(f"数据库错误：{e}")

db_handler.close_connection()